import sqlite3
from string import  Template
from typing import *

class DAO():
    def select_row_by_range(self, tb_name:str, state_column:str, index:str, start:int, end:int):
        raise NotImplementedError()

    def select_row_finished(self, tb_name: str, state_column: str, index: str, start: int, end: int):
        raise NotImplementedError()

    def update_a_row(self, tb_name: str, index: str, id: int, attribute: str, value: int):
        raise NotImplementedError()

class DAOImpl(DAO):
    def __init__(self, db_name:str):
        self.conn = sqlite3.connect(db_name)
        self.conn.row_factory = self.dict_factory

    def dict_factory(self, cursor, row):
        """#让sqlite以字典形式返回数据"""
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d

    def select_row_by_range(self, tb_name:str, state_column:str, index:str, start:int, end:int)->Dict:
        """返回一条没有被标注过的数据"""
        cursor = self.conn.cursor()
        template = Template("select * from $tb_name where $state_column is null and $index > $start and $index <= $end ")
        sql = template.substitute(tb_name = tb_name, state_column = state_column, index = index, start = start, end = end)
        #print(sql)
        cursor.execute(sql)
        try:
            record = next(cursor)
            cursor.close()
            return record
        except:
            cursor.close()
            return None

    def select_row_finished(self, tb_name:str, state_column:str, index:str, start:int, end:int)->Tuple[int, int]:
        """计算已经标注的个数"""
        cursor = self.conn.cursor()
        #查询worker已经浏览的个数
        template = Template(
            "select count(*) as num from $tb_name where $state_column is not null and $index > $start and $index <= $end ")
        sql = template.substitute(tb_name=tb_name, state_column=state_column, index=index, start=start, end=end)
        # print(sql)
        cursor.execute(sql)
        num_viewed = next(cursor)["num"]
        #查询worker实际更改的个数
        template = Template(
            "select count(*) as num from $tb_name where $state_column == 2 and $index > $start and $index <= $end ")
        sql = template.substitute(tb_name=tb_name, state_column=state_column, index=index, start=start, end=end)
        #print(sql)
        cursor.execute(sql)
        num_updated = next(cursor)["num"]
        # 返回最后的查询结果
        cursor.close()
        return num_viewed, num_updated

    def update_a_row(self, tb_name:str, index:str, id:int, attribute:str, value:int)->int:
        """更新一条记录"""
        cursor = self.conn.cursor()
        template = Template(
            "select ($attribute != $value) as updated from $tb_name where $index = $id ")
        sql = template.substitute(tb_name=tb_name, attribute=attribute, value=value,  index=index, id=id)
        cursor.execute(sql)
        update_state = next(cursor)["updated"]
        template = Template(
            "update $tb_name set $attribute = $value where $index = $id ")
        sql = template.substitute(tb_name=tb_name, attribute=attribute, value=value,  index=index, id=id)
        # print(sql)
        cursor.execute(sql)
        self.conn.commit()
        cursor.close()
        return update_state

    def close(self):
        self.conn.close()